package com.travel.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import com.travel.dao.UserDao;
import com.travel.entity.User;
import com.travel.util.DBUtils;
import com.travel.util.PageInfo;

public class UserDaoImpl implements UserDao {

	private DBUtils  db = new DBUtils();

	@Override
	public List<User> findByUserName(String userName) {
		List<User> list = new ArrayList<User>();
		ResultSet rs = db.query(
				"SELECT user_id,user_name,user_password,user_sex,user_birth,user_address,user_phone,user_picture FROM tab_user WHERE user_name like concat('%',?,'%')",
				userName);
		try {
			while (rs.next()) {
				Integer userId = rs.getInt("user_id");
				userName = rs.getString("user_name");
				String userPassword = rs.getString("user_password");
				Integer userSex = rs.getInt("user_sex");
				String userBirth = rs.getString("user_birth");
				String userAddress = rs.getString("user_address");
				String userPhone = rs.getString("user_phone");
				String userPicture = rs.getString("user_picture");
				list.add(new User(userId, userName, userPassword, userSex, userBirth, userAddress, userPhone,
						userPicture));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int insert(User user) {
		// TODO Auto-generated method stub
		return db.update("INSERT into tab_user(user_id,user_name,user_password,user_sex,user_birth,user_address,user_phone,user_picture) VALUES (?,?,?,?,?,?,?,?)", user.getUserId(),user.getUserName(),user.getUserPassword(),user.getUserSex(),user.getUserBirth(),user.getUserAddress(),user.getUserPhone(),user.getUserPicture());
	}

	@Override
	public List<User> find() {
		// TODO Auto-generated method stub
		List<User> list = new ArrayList<User>();
		ResultSet rs = db.query(
				"SELECT user_id,user_name,user_password,user_sex,user_birth,user_address,user_phone,user_picture FROM tab_user");
		try {
			while (rs.next()) {
				Integer userId = rs.getInt("user_id");
				String userName = rs.getString("user_name");
				String userPassword = rs.getString("user_password");
				Integer userSex = rs.getInt("user_sex");
				String userBirth = rs.getString("user_birth");
				String userAddress = rs.getString("user_address");
				String userPhone = rs.getString("user_phone");
				String userPicture = rs.getString("user_picture");
				list.add(new User(userId, userName, userPassword, userSex, userBirth, userAddress, userPhone,
						userPicture));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int deletById(int userId) {
		// TODO Auto-generated method stub
		return db.update("delete from tab_user where user_id=?",userId);
	}

	@Override
	public List<User> findByUserNameAndUserPassword(String userName, String userPassword) {
		List<User> list=new ArrayList<User>();
		ResultSet rs=db.query("SELECT user_id,user_name,user_password,user_sex,user_birth,user_address,user_phone,user_picture FROM tab_user WHERE user_name=? AND user_password=?",userName,userPassword);
		try {
			while (rs.next()) {
				Integer userId = rs.getInt("user_id");
				userName = rs.getString("user_name");
				userPassword = rs.getString("user_password");
				Integer userSex = rs.getInt("user_sex");
				String userBirth = rs.getString("user_birth");
				String userAddress = rs.getString("user_address");
				String userPhone = rs.getString("user_phone");
				String userPicture = rs.getString("user_picture");
				list.add(new User(userId, userName, userPassword, userSex, userBirth, userAddress, userPhone,
						userPicture));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int updateUser(int userId, User user) {
		String sql="UPDATE tab_user SET user_name=?,user_password=?,user_sex=?,user_birth=?,user_address=?,user_phone=?,user_picture=? WHERE user_id=?";
		int update=db.update(sql, user.getUserName(),user.getUserPassword(),user.getUserSex(),user.getUserBirth(),user.getUserAddress(),user.getUserPhone(),user.getUserPicture(),userId);
		return update;
	}

	@Override
	public PageInfo<User> findByPage(int pageNum, int pageSize,String userName) {
		// 1.获取分页结果的数据
				List<User> data = new ArrayList<User>();
				
				StringBuffer sb = new StringBuffer();
				sb.append("select")
				  .append(" user_id,")
				  .append(" user_name,")
				  .append(" user_password,")
				  .append(" user_sex,")
				  .append(" user_birth,")
				  .append(" user_address,")
				  .append(" user_phone,")
				  .append(" user_picture")
				  .append(" from tab_user ")
				  .append(" where user_name like concat('%',?,'%')")
				  .append(" limit ?,?");
				
				ResultSet rs  = db.query(sb.toString(),userName ,(pageNum-1)*pageSize, pageSize);
				// 遍历结果集
				try {
					while (rs.next()) {
						Integer userId = rs.getInt("user_id");
						String userName2 = rs.getString("user_name");
						String userPassword = rs.getString("user_password");
						Integer userSex = rs.getInt("user_sex");
						String userBirth = rs.getString("user_birth");
						String userAddress = rs.getString("user_address");
						String userPhone = rs.getString("user_phone");
						String userPicture = rs.getString("user_picture");
						
						// 构建实体模型
						User user = new User(userId, userName2, userPassword, userSex, userBirth, userAddress, userPhone, userPicture);
				
						// 添加到集合中
						data.add(user);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				// 2.获取总记录数(未分页之前)
				int total = 0;
				sb = new StringBuffer();
				sb.append("select")
				  .append(" count(*)")
				  .append(" from tab_user ")
				  .append(" where user_name like concat('%',?,'%')");
				
				rs  = db.query(sb.toString(),userName);
				// 遍历结果集
				try {
					while (rs.next()) {
						total  = rs.getInt(1);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				// 构建分页对象
				PageInfo<User> pageInfo = new PageInfo<User>(data, pageNum, pageSize, total);
				
				return pageInfo;
	}

}
